{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT names\n",
    "\n",
    "## Pattern Matching Strings\n",
    "This tutorial uses the **LIKE** operator to check names. We will be using the SELECT command on the table world:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.3\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.0     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "\n",
    "You can use `WHERE name LIKE 'B%'` to find the countries that start with \"B\".\n",
    "\n",
    "The % is a _wild-card_ it can match any characters\n",
    "\n",
    "**Find the country that start with Y**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "world <- dbReadTable(con, 'world')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Yemen</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Yemen\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Yemen |\n",
       "\n"
      ],
      "text/plain": [
       "  name \n",
       "1 Yemen"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_starts(name, '[Yy]')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "\n",
    "**Find the countries that end with y**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 8 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Germany     </td></tr>\n",
       "\t<tr><td>Hungary     </td></tr>\n",
       "\t<tr><td>Italy       </td></tr>\n",
       "\t<tr><td>Norway      </td></tr>\n",
       "\t<tr><td>Paraguay    </td></tr>\n",
       "\t<tr><td>Turkey      </td></tr>\n",
       "\t<tr><td>Uruguay     </td></tr>\n",
       "\t<tr><td>Vatican City</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 8 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Germany     \\\\\n",
       "\t Hungary     \\\\\n",
       "\t Italy       \\\\\n",
       "\t Norway      \\\\\n",
       "\t Paraguay    \\\\\n",
       "\t Turkey      \\\\\n",
       "\t Uruguay     \\\\\n",
       "\t Vatican City\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 8 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Germany      |\n",
       "| Hungary      |\n",
       "| Italy        |\n",
       "| Norway       |\n",
       "| Paraguay     |\n",
       "| Turkey       |\n",
       "| Uruguay      |\n",
       "| Vatican City |\n",
       "\n"
      ],
      "text/plain": [
       "  name        \n",
       "1 Germany     \n",
       "2 Hungary     \n",
       "3 Italy       \n",
       "4 Norway      \n",
       "5 Paraguay    \n",
       "6 Turkey      \n",
       "7 Uruguay     \n",
       "8 Vatican City"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_ends(name, '[Yy]')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "\n",
    "Luxembourg has an **x** - so does one other country. List them both.\n",
    "\n",
    "**Find the countries that contain the letter x**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 2 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Luxembourg</td></tr>\n",
       "\t<tr><td>Mexico    </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 2 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Luxembourg\\\\\n",
       "\t Mexico    \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 2 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Luxembourg |\n",
       "| Mexico     |\n",
       "\n"
      ],
      "text/plain": [
       "  name      \n",
       "1 Luxembourg\n",
       "2 Mexico    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, '[Xx]')) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "\n",
    "Iceland, Switzerland end with **land** - but are there others?\n",
    "\n",
    "**Find the countries that end with land**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 8 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Finland    </td></tr>\n",
       "\t<tr><td>Iceland    </td></tr>\n",
       "\t<tr><td>Ireland    </td></tr>\n",
       "\t<tr><td>New Zealand</td></tr>\n",
       "\t<tr><td>Poland     </td></tr>\n",
       "\t<tr><td>Swaziland  </td></tr>\n",
       "\t<tr><td>Switzerland</td></tr>\n",
       "\t<tr><td>Thailand   </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 8 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Finland    \\\\\n",
       "\t Iceland    \\\\\n",
       "\t Ireland    \\\\\n",
       "\t New Zealand\\\\\n",
       "\t Poland     \\\\\n",
       "\t Swaziland  \\\\\n",
       "\t Switzerland\\\\\n",
       "\t Thailand   \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 8 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Finland     |\n",
       "| Iceland     |\n",
       "| Ireland     |\n",
       "| New Zealand |\n",
       "| Poland      |\n",
       "| Swaziland   |\n",
       "| Switzerland |\n",
       "| Thailand    |\n",
       "\n"
      ],
      "text/plain": [
       "  name       \n",
       "1 Finland    \n",
       "2 Iceland    \n",
       "3 Ireland    \n",
       "4 New Zealand\n",
       "5 Poland     \n",
       "6 Swaziland  \n",
       "7 Switzerland\n",
       "8 Thailand   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_ends(name, 'land')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "\n",
    "Columbia starts with a **C** and ends with **ia** - there are two more like this.\n",
    "\n",
    "**Find the countries that start with C and end with ia**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Cambodia</td></tr>\n",
       "\t<tr><td>Colombia</td></tr>\n",
       "\t<tr><td>Croatia </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Cambodia\\\\\n",
       "\t Colombia\\\\\n",
       "\t Croatia \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Cambodia |\n",
       "| Colombia |\n",
       "| Croatia  |\n",
       "\n"
      ],
      "text/plain": [
       "  name    \n",
       "1 Cambodia\n",
       "2 Colombia\n",
       "3 Croatia "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, '^[Cc].*ia$')) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Greece has a double **e** - who has **a** double **o**?\n",
    "\n",
    "**Find the country that has oo in the name**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Cameroon</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Cameroon\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Cameroon |\n",
       "\n"
      ],
      "text/plain": [
       "  name    \n",
       "1 Cameroon"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, 'oo')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "\n",
    "Bahamas has three **a** - who else?\n",
    "\n",
    "**Find the countries that have three or more a in the name**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 21 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Antigua and Barbuda   </td></tr>\n",
       "\t<tr><td>Bahamas               </td></tr>\n",
       "\t<tr><td>Bosnia and Herzegovina</td></tr>\n",
       "\t<tr><td>Canada                </td></tr>\n",
       "\t<tr><td>Equatorial Guinea     </td></tr>\n",
       "\t<tr><td>Guatemala             </td></tr>\n",
       "\t<tr><td>Jamaica               </td></tr>\n",
       "\t<tr><td>Kazakhstan            </td></tr>\n",
       "\t<tr><td>Madagascar            </td></tr>\n",
       "\t<tr><td>Malaysia              </td></tr>\n",
       "\t<tr><td>...</td></tr>\n",
       "\t<tr><td>Mauritania                      </td></tr>\n",
       "\t<tr><td>Micronesia, Federated States of </td></tr>\n",
       "\t<tr><td>Nicaragua                       </td></tr>\n",
       "\t<tr><td>Panama                          </td></tr>\n",
       "\t<tr><td>Papua New Guinea                </td></tr>\n",
       "\t<tr><td>Paraguay                        </td></tr>\n",
       "\t<tr><td>Saint Vincent and the Grenadines</td></tr>\n",
       "\t<tr><td>Saudi Arabia                    </td></tr>\n",
       "\t<tr><td>Tanzania                        </td></tr>\n",
       "\t<tr><td>Trinidad and Tobago             </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 21 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Antigua and Barbuda   \\\\\n",
       "\t Bahamas               \\\\\n",
       "\t Bosnia and Herzegovina\\\\\n",
       "\t Canada                \\\\\n",
       "\t Equatorial Guinea     \\\\\n",
       "\t Guatemala             \\\\\n",
       "\t Jamaica               \\\\\n",
       "\t Kazakhstan            \\\\\n",
       "\t Madagascar            \\\\\n",
       "\t Malaysia              \\\\\n",
       "\t ...\\\\\n",
       "\t Mauritania                      \\\\\n",
       "\t Micronesia, Federated States of \\\\\n",
       "\t Nicaragua                       \\\\\n",
       "\t Panama                          \\\\\n",
       "\t Papua New Guinea                \\\\\n",
       "\t Paraguay                        \\\\\n",
       "\t Saint Vincent and the Grenadines\\\\\n",
       "\t Saudi Arabia                    \\\\\n",
       "\t Tanzania                        \\\\\n",
       "\t Trinidad and Tobago             \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 21 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Antigua and Barbuda    |\n",
       "| Bahamas                |\n",
       "| Bosnia and Herzegovina |\n",
       "| Canada                 |\n",
       "| Equatorial Guinea      |\n",
       "| Guatemala              |\n",
       "| Jamaica                |\n",
       "| Kazakhstan             |\n",
       "| Madagascar             |\n",
       "| Malaysia               |\n",
       "| ... |\n",
       "| Mauritania                       |\n",
       "| Micronesia, Federated States of  |\n",
       "| Nicaragua                        |\n",
       "| Panama                           |\n",
       "| Papua New Guinea                 |\n",
       "| Paraguay                         |\n",
       "| Saint Vincent and the Grenadines |\n",
       "| Saudi Arabia                     |\n",
       "| Tanzania                         |\n",
       "| Trinidad and Tobago              |\n",
       "\n"
      ],
      "text/plain": [
       "    name                            \n",
       "1   Antigua and Barbuda             \n",
       "2   Bahamas                         \n",
       "3   Bosnia and Herzegovina          \n",
       "4   Canada                          \n",
       "5   Equatorial Guinea               \n",
       "6   Guatemala                       \n",
       "7   Jamaica                         \n",
       "8   Kazakhstan                      \n",
       "9   Madagascar                      \n",
       "10  Malaysia                        \n",
       "... ...                             \n",
       "12  Mauritania                      \n",
       "13  Micronesia, Federated States of \n",
       "14  Nicaragua                       \n",
       "15  Panama                          \n",
       "16  Papua New Guinea                \n",
       "17  Paraguay                        \n",
       "18  Saint Vincent and the Grenadines\n",
       "19  Saudi Arabia                    \n",
       "20  Tanzania                        \n",
       "21  Trinidad and Tobago             "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, 'a.*a.*a')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "\n",
    "India and Angola have an **n** as the second character. You can use the underscore as a single character wildcard.\n",
    "\n",
    "```sql\n",
    "SELECT name FROM world\n",
    " WHERE name LIKE '_n%'\n",
    "ORDER BY name\n",
    "```\n",
    "\n",
    "**Find the countries that have \"t\" as the second character.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 2 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Ethiopia</td></tr>\n",
       "\t<tr><td>Italy   </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 2 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Ethiopia\\\\\n",
       "\t Italy   \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 2 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Ethiopia |\n",
       "| Italy    |\n",
       "\n"
      ],
      "text/plain": [
       "  name    \n",
       "1 Ethiopia\n",
       "2 Italy   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, '^.{1}t')) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "\n",
    "Lesotho and Moldova both have two o characters separated by two other characters.\n",
    "\n",
    "**Find the countries that have two \"o\" characters separated by two others.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 7 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Congo, Democratic Republic of</td></tr>\n",
       "\t<tr><td>Congo, Republic of           </td></tr>\n",
       "\t<tr><td>Lesotho                      </td></tr>\n",
       "\t<tr><td>Moldova                      </td></tr>\n",
       "\t<tr><td>Mongolia                     </td></tr>\n",
       "\t<tr><td>Morocco                      </td></tr>\n",
       "\t<tr><td>Sao Tomé and Príncipe        </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 7 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Congo, Democratic Republic of\\\\\n",
       "\t Congo, Republic of           \\\\\n",
       "\t Lesotho                      \\\\\n",
       "\t Moldova                      \\\\\n",
       "\t Mongolia                     \\\\\n",
       "\t Morocco                      \\\\\n",
       "\t Sao Tomé and Príncipe        \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 7 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Congo, Democratic Republic of |\n",
       "| Congo, Republic of            |\n",
       "| Lesotho                       |\n",
       "| Moldova                       |\n",
       "| Mongolia                      |\n",
       "| Morocco                       |\n",
       "| Sao Tomé and Príncipe         |\n",
       "\n"
      ],
      "text/plain": [
       "  name                         \n",
       "1 Congo, Democratic Republic of\n",
       "2 Congo, Republic of           \n",
       "3 Lesotho                      \n",
       "4 Moldova                      \n",
       "5 Mongolia                     \n",
       "6 Morocco                      \n",
       "7 Sao Tomé and Príncipe        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>%\n",
    "    filter(str_detect(name, 'o.{2}o')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "\n",
    "Cuba and Togo have four characters names.\n",
    "\n",
    "**Find the countries that have exactly four characters.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 10 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Chad</td></tr>\n",
       "\t<tr><td>Cuba</td></tr>\n",
       "\t<tr><td>Fiji</td></tr>\n",
       "\t<tr><td>Iran</td></tr>\n",
       "\t<tr><td>Iraq</td></tr>\n",
       "\t<tr><td>Laos</td></tr>\n",
       "\t<tr><td>Mali</td></tr>\n",
       "\t<tr><td>Oman</td></tr>\n",
       "\t<tr><td>Peru</td></tr>\n",
       "\t<tr><td>Togo</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 10 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Chad\\\\\n",
       "\t Cuba\\\\\n",
       "\t Fiji\\\\\n",
       "\t Iran\\\\\n",
       "\t Iraq\\\\\n",
       "\t Laos\\\\\n",
       "\t Mali\\\\\n",
       "\t Oman\\\\\n",
       "\t Peru\\\\\n",
       "\t Togo\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 10 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Chad |\n",
       "| Cuba |\n",
       "| Fiji |\n",
       "| Iran |\n",
       "| Iraq |\n",
       "| Laos |\n",
       "| Mali |\n",
       "| Oman |\n",
       "| Peru |\n",
       "| Togo |\n",
       "\n"
      ],
      "text/plain": [
       "   name\n",
       "1  Chad\n",
       "2  Cuba\n",
       "3  Fiji\n",
       "4  Iran\n",
       "5  Iraq\n",
       "6  Laos\n",
       "7  Mali\n",
       "8  Oman\n",
       "9  Peru\n",
       "10 Togo"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(name, '^.{4}$')) %>%\n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "\n",
    "The capital of **Luxembourg** is **Luxembourg**. Show all the countries where the capital is the same as the name of the country\n",
    "\n",
    "**Find the country where the name is the capital city.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 4 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Djibouti  </td></tr>\n",
       "\t<tr><td>Luxembourg</td></tr>\n",
       "\t<tr><td>San Marino</td></tr>\n",
       "\t<tr><td>Singapore </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Djibouti  \\\\\n",
       "\t Luxembourg\\\\\n",
       "\t San Marino\\\\\n",
       "\t Singapore \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Djibouti   |\n",
       "| Luxembourg |\n",
       "| San Marino |\n",
       "| Singapore  |\n",
       "\n"
      ],
      "text/plain": [
       "  name      \n",
       "1 Djibouti  \n",
       "2 Luxembourg\n",
       "3 San Marino\n",
       "4 Singapore "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(name==capital) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "\n",
    "The capital of **Mexico** is **Mexico City**. Show all the countries where the capital has the country together with the word \"City\".\n",
    "\n",
    "**Find the country where the capital is the country plus \"City\".**\n",
    "\n",
    "> _The concat function_    \n",
    "> The function concat is short for concatenate - you can use it to combine two or more strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 4 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Guatemala</td></tr>\n",
       "\t<tr><td>Kuwait   </td></tr>\n",
       "\t<tr><td>Mexico   </td></tr>\n",
       "\t<tr><td>Panama   </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 1\n",
       "\\begin{tabular}{l}\n",
       " name\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Guatemala\\\\\n",
       "\t Kuwait   \\\\\n",
       "\t Mexico   \\\\\n",
       "\t Panama   \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 1\n",
       "\n",
       "| name &lt;chr&gt; |\n",
       "|---|\n",
       "| Guatemala |\n",
       "| Kuwait    |\n",
       "| Mexico    |\n",
       "| Panama    |\n",
       "\n"
      ],
      "text/plain": [
       "  name     \n",
       "1 Guatemala\n",
       "2 Kuwait   \n",
       "3 Mexico   \n",
       "4 Panama   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(capital==paste(name, 'City')) %>% \n",
    "    select(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "\n",
    "**Find the capital and the name where the capital includes the name of the country.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 10 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>capital</th><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Andorra la Vella</td><td>Andorra   </td></tr>\n",
       "\t<tr><td>Djibouti        </td><td>Djibouti  </td></tr>\n",
       "\t<tr><td>Guatemala City  </td><td>Guatemala </td></tr>\n",
       "\t<tr><td>Kuwait City     </td><td>Kuwait    </td></tr>\n",
       "\t<tr><td>Luxembourg      </td><td>Luxembourg</td></tr>\n",
       "\t<tr><td>Mexico City     </td><td>Mexico    </td></tr>\n",
       "\t<tr><td>Monaco-Ville    </td><td>Monaco    </td></tr>\n",
       "\t<tr><td>Panama City     </td><td>Panama    </td></tr>\n",
       "\t<tr><td>San Marino      </td><td>San Marino</td></tr>\n",
       "\t<tr><td>Singapore       </td><td>Singapore </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 10 × 2\n",
       "\\begin{tabular}{ll}\n",
       " capital & name\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Andorra la Vella & Andorra   \\\\\n",
       "\t Djibouti         & Djibouti  \\\\\n",
       "\t Guatemala City   & Guatemala \\\\\n",
       "\t Kuwait City      & Kuwait    \\\\\n",
       "\t Luxembourg       & Luxembourg\\\\\n",
       "\t Mexico City      & Mexico    \\\\\n",
       "\t Monaco-Ville     & Monaco    \\\\\n",
       "\t Panama City      & Panama    \\\\\n",
       "\t San Marino       & San Marino\\\\\n",
       "\t Singapore        & Singapore \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 10 × 2\n",
       "\n",
       "| capital &lt;chr&gt; | name &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Andorra la Vella | Andorra    |\n",
       "| Djibouti         | Djibouti   |\n",
       "| Guatemala City   | Guatemala  |\n",
       "| Kuwait City      | Kuwait     |\n",
       "| Luxembourg       | Luxembourg |\n",
       "| Mexico City      | Mexico     |\n",
       "| Monaco-Ville     | Monaco     |\n",
       "| Panama City      | Panama     |\n",
       "| San Marino       | San Marino |\n",
       "| Singapore        | Singapore  |\n",
       "\n"
      ],
      "text/plain": [
       "   capital          name      \n",
       "1  Andorra la Vella Andorra   \n",
       "2  Djibouti         Djibouti  \n",
       "3  Guatemala City   Guatemala \n",
       "4  Kuwait City      Kuwait    \n",
       "5  Luxembourg       Luxembourg\n",
       "6  Mexico City      Mexico    \n",
       "7  Monaco-Ville     Monaco    \n",
       "8  Panama City      Panama    \n",
       "9  San Marino       San Marino\n",
       "10 Singapore        Singapore "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>%\n",
    "    filter(str_detect(capital, name)) %>% \n",
    "    select(capital, name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "\n",
    "**Find the capital and the name where the capital is an extension of name of the country.**\n",
    "\n",
    "You _should_ include **Mexico City** as it is longer than **Mexico**. You _should not_ include **Luxembourg** as the capital is the same as the country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>capital</th><th scope=col>name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Andorra la Vella</td><td>Andorra  </td></tr>\n",
       "\t<tr><td>Guatemala City  </td><td>Guatemala</td></tr>\n",
       "\t<tr><td>Kuwait City     </td><td>Kuwait   </td></tr>\n",
       "\t<tr><td>Mexico City     </td><td>Mexico   </td></tr>\n",
       "\t<tr><td>Monaco-Ville    </td><td>Monaco   </td></tr>\n",
       "\t<tr><td>Panama City     </td><td>Panama   </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " capital & name\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Andorra la Vella & Andorra  \\\\\n",
       "\t Guatemala City   & Guatemala\\\\\n",
       "\t Kuwait City      & Kuwait   \\\\\n",
       "\t Mexico City      & Mexico   \\\\\n",
       "\t Monaco-Ville     & Monaco   \\\\\n",
       "\t Panama City      & Panama   \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| capital &lt;chr&gt; | name &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Andorra la Vella | Andorra   |\n",
       "| Guatemala City   | Guatemala |\n",
       "| Kuwait City      | Kuwait    |\n",
       "| Mexico City      | Mexico    |\n",
       "| Monaco-Ville     | Monaco    |\n",
       "| Panama City      | Panama    |\n",
       "\n"
      ],
      "text/plain": [
       "  capital          name     \n",
       "1 Andorra la Vella Andorra  \n",
       "2 Guatemala City   Guatemala\n",
       "3 Kuwait City      Kuwait   \n",
       "4 Mexico City      Mexico   \n",
       "5 Monaco-Ville     Monaco   \n",
       "6 Panama City      Panama   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(capital, name) & capital != name) %>% \n",
    "    select(capital, name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "\n",
    "For **Monaco-Ville** the name is **Monaco** and the extension is **-Ville**.\n",
    "\n",
    "**Show the name and the extension where the capital is an extension of name of the country.**\n",
    "\n",
    "You can use the SQL function [REPLACE](https://sqlzoo.net/wiki/REPLACE)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>extension</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Andorra  </td><td> la Vella</td></tr>\n",
       "\t<tr><td>Guatemala</td><td> City    </td></tr>\n",
       "\t<tr><td>Kuwait   </td><td> City    </td></tr>\n",
       "\t<tr><td>Mexico   </td><td> City    </td></tr>\n",
       "\t<tr><td>Monaco   </td><td>-Ville   </td></tr>\n",
       "\t<tr><td>Panama   </td><td> City    </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & extension\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Andorra   &  la Vella\\\\\n",
       "\t Guatemala &  City    \\\\\n",
       "\t Kuwait    &  City    \\\\\n",
       "\t Mexico    &  City    \\\\\n",
       "\t Monaco    & -Ville   \\\\\n",
       "\t Panama    &  City    \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| name &lt;chr&gt; | extension &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Andorra   |  la Vella |\n",
       "| Guatemala |  City     |\n",
       "| Kuwait    |  City     |\n",
       "| Mexico    |  City     |\n",
       "| Monaco    | -Ville    |\n",
       "| Panama    |  City     |\n",
       "\n"
      ],
      "text/plain": [
       "  name      extension\n",
       "1 Andorra    la Vella\n",
       "2 Guatemala  City    \n",
       "3 Kuwait     City    \n",
       "4 Mexico     City    \n",
       "5 Monaco    -Ville   \n",
       "6 Panama     City    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(str_detect(capital, paste('^', name, '.+$', sep=''))) %>% \n",
    "    mutate(extension=str_replace(capital, name, '')) %>% \n",
    "    select(name, extension)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
